using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Collections.Generic;

/// <summary>
/// Summary description for ProductCategory
/// </summary> 
public class ProductCategoryDAO : DBConnection
{
    #region sql command
    string SQL_SELECT_ID = "";
    string SQL_SELECT_ALL = "select * from ProductCategory";
    string SQL_SELECT_BY_NAME = "select * from ProductCategory where CategoryName=@name";
    string SQL_INSERT = "insert into ProductCategory(CategoryName) values(@categoryName)";
    string SQL_UPDATE = "update ProductCategory set CategoryName = @categoryName where ProductCategoryID = @productCategoryId";
    string SQL_DELETE = "delete ProductCategory where ProductCategoryID = @productCategoryId";
    #endregion

    #region fields
    string message = "";

    #endregion

    #region constructors
    public ProductCategoryDAO()
    {
    }
    #endregion

    #region properties

    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region methods
    public IList<ProductCategory> GetAll()
    {
        IList<ProductCategory> list = new List<ProductCategory>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    ProductCategory cate = new ProductCategory();
                    cate.ID = dr.GetInt32(0);
                    cate.Name = dr.GetString(1);
                    list.Add(cate);
                }
                
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public IList<ProductCategory> GetCategory(string name)
    {
        IList<ProductCategory> list = new List<ProductCategory>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            command.Parameters.AddWithValue("@name",name);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    ProductCategory cate = new ProductCategory();
                    cate.ID = dr.GetInt32(0);
                    cate.Name = dr.GetString(1);
                    list.Add(cate);
                }

            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public int InsertCategory(ProductCategory proCate)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT, conn);
            cmd.Parameters.AddWithValue("@categoryName", proCate.Name);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    public int UpdateCategory(ProductCategory proCate)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE, conn);
            cmd.Parameters.AddWithValue("@productCategoryId", proCate.ID);
            cmd.Parameters.AddWithValue("@categoryName", proCate.Name);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    public int DeleteCategory(ProductCategory proCate)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE, conn);
            cmd.Parameters.AddWithValue("@productCategoryId", proCate.ID);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }
    #endregion
}
